package com.mvetc.util;

import java.sql.*;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

/**
 * JDBC-MYSQL数据库工具类
 * 
 * @author Administrator
 *
 */
/**
 * @author Administrator
 *
 */
public class DBUtil {
	// 静态常量
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/movie?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&useAffectedRows=true&serverTimezone=GMT";
	private static final String USER = "root";
	private static final String PASSWORD = "root";

	/**
	 * 连接数据库
	 * 
	 * @return 连接对象
	 */
	private static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 执行SELECT查询语句
	 * @param sql 执行SQL语句
	 * @param params SQL语句中所需参数
	 * @return 缓存结果集对象
	 */
	public static ResultSet doQuery(String sql,Object...params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;// 结果集
		CachedRowSet cachedRowSet = null;	// 缓存结果集
		try {
			// 连接数据库
			conn = getConnection();
			// 创建预编译语句对象
			ps = conn.prepareStatement(sql);
			// 通过循环可变参数数组,对?赋值
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]); 
			}
			// 执行SQL-->得到结果集
			rs = ps.executeQuery();
			
			cachedRowSet = new CachedRowSetImpl();
			cachedRowSet.populate(rs);	// void populate(ResultSet data) 将ResultSet对象中的数据存储到缓存结果集对象中
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			closeAll(rs, ps, conn);
		}
		return cachedRowSet;  //返回缓存结果集对象,CachedRowSet是继承于ResultSet
	}
	
	/**
	 * 执行insert/update/delete语句
	 * @param sql 执行SQL语句
	 * @param params SQL语句中所需参数
	 * @return 影响行数
	 */
	public static int doUpdate(String sql,Object...params) {
		int n = 0;
		Connection conn = null;
		PreparedStatement ps = null;// 预编译语句对象
		try {
			conn = getConnection();

			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			n = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭
			closeAll(null, ps, conn);
		}
		return n;
	}
	
	/**
	 * 关闭数据库资源
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	private  static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 查询语句拼接
	 * @author SunZhiQiang
	 * @param selectColumnsName
	 * @param tableNames
	 * @param joinColumnsName
	 * @return
	 */
	public static StringBuilder selectSql(String[] selectColumnsName,String[] tableNames,String[] joinColumnsName,String...where) {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("select ");
			for (int i = 0; i < selectColumnsName.length; i++) {
				if (i==selectColumnsName.length-1) {
					sBuilder.append(selectColumnsName[i]);
				}else {
					sBuilder.append(selectColumnsName[i]+", ");
				}
			}
			sBuilder.append(" from "+tableNames[0]);
			for (int i = 1; i < tableNames.length; i++) {
				sBuilder.append(" inner join "+tableNames[i]);
				sBuilder.append(" on "+tableNames[i-1]+"."+joinColumnsName[i-1]);
				sBuilder.append("="+tableNames[i]+"."+joinColumnsName[i-1]);
			}
			if (where[0].contains("=")) {
				sBuilder.append(" where "+where[0]);
			}else {
				sBuilder.append(" "+where[0]);
			}
			return sBuilder;
		}
		
		
	/**
	 * 插入语句拼接
	 * @author SunZhiQiang
	 * @param tableName
	 * @param columns
	 * @param values
	 * @return
	 */
	public static StringBuilder insertSql(String tableName, String[] columns, String[] values) {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("insert into "+tableName+" ");
			for (int i = 0; i < columns.length; i++) {
				if (i==0) {
					sBuilder.append("("+columns[i]);
				}else if (i==columns.length-1) {
					sBuilder.append(", "+columns[i]+")");
				}else {
					sBuilder.append(", "+columns[i]);
				}
				
			}
			sBuilder.append(" values ");
			for (int i = 0; i < values.length; i++) {
				if (i==0) {
					sBuilder.append("('"+values[i]);
				}else if (i==values.length-1) {
					sBuilder.append("', '"+values[i]+"')");
				}else {
					sBuilder.append("', '"+values[i]);
				}
				
			}
			
			return sBuilder;
		}
		
		
	/**
	 * 删除语句拼接
	 * @author SunZhiQiang
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static StringBuilder deleteSql(String tableName, Map<String, String> where) {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("delete from "+tableName+" where ");
			Set<Entry<String, String>> entrySet = where.entrySet();
			int a = 0;
			for (Entry<String, String> entry : entrySet) {
				a++;
				if (a >1) {
					sBuilder.append(" and "+entry.getKey()+"='"+entry.getValue()+"'");
				} else {
					sBuilder.append(entry.getKey()+"='"+entry.getValue()+"'");
				}
				
			}
			return sBuilder;
		}
		
		
	/**
	 * 更新语句拼接
	 * @author SunZhiQiang
	 * @param tableName
	 * @param set
	 * @param where
	 * @return
	 */
	public static StringBuilder updateSql(String tableName, Map<String, String> set, Map<String, String> where) {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("update "+tableName+" set ");
			Set<Entry<String, String>> entrySet = set.entrySet();
			Object[] array = entrySet.toArray();
			for (int i = 0; i < array.length; i++) {
				if (i==array.length-1) {
					sBuilder.append(array[i]);
				}else {
					sBuilder.append(array[i]+", ");
				}
			}
			sBuilder.append(" where ");
			Set<Entry<String, String>> entrySet2 = where.entrySet();
			for (Entry<String, String> entry : entrySet2) {
				sBuilder.append(entry.getKey()+"="+entry.getValue());
				
			}
			return sBuilder;
		}
}
